/*
 Navicat Premium Dump SQL

 Source Server         : docker
 Source Server Type    : MySQL
 Source Server Version : 80042 (8.0.42)
 Source Host           : localhost:3306
 Source Schema         : med_appointment_db

 Target Server Type    : MySQL
 Target Server Version : 80042 (8.0.42)
 File Encoding         : 65001

 Date: 13/08/2025 11:36:27
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for appointment
-- ----------------------------
DROP TABLE IF EXISTS `appointment`;
CREATE TABLE `appointment` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `channel` tinyint NOT NULL COMMENT '渠道：1预约线下/2预约线上排班',
  `user_id` bigint NOT NULL COMMENT '用户ID（患者所属用户方便查询）',
  `patient_id` bigint NOT NULL COMMENT '就诊人ID → patient_profile.id',
  `staff_id` bigint DEFAULT NULL COMMENT '医生/医护人员ID',
  `slot_no` bigint DEFAULT NULL COMMENT '对应的排班时段编号',
  `status` tinyint NOT NULL DEFAULT '0' COMMENT '0待支付/1已支付/2已取消/3爽约/4已完成',
  `order_id` bigint DEFAULT NULL COMMENT '订单ID → order_master.id（待支付可空）',
  `booked_at` datetime NOT NULL COMMENT '预约时间',
  `cancel_reason` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL COMMENT '取消原因（可空）',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `clnic_id` bigint DEFAULT NULL COMMENT '坐诊id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci COMMENT='预约记录表';

-- ----------------------------
-- Records of appointment
-- ----------------------------
BEGIN;
INSERT INTO `appointment` (`id`, `channel`, `patient_id`, `staff_id`, `slot_no`, `status`, `order_id`, `booked_at`, `cancel_reason`, `created_at`, `updated_at`, `clnic_id`) VALUES (1, 1, 1, 1, 1, 1, 1, '2025-08-13 11:03:37', '1', '2025-08-13 11:03:43', '2025-08-13 11:03:47', NULL);
COMMIT;

-- ----------------------------
-- Table structure for clnic_staff
-- ----------------------------
DROP TABLE IF EXISTS `clnic_staff`;
CREATE TABLE `clnic_staff` (
  `id` bigint NOT NULL COMMENT '坐诊id',
  `clnic_time` datetime NOT NULL COMMENT '坐诊日期',
  `staff_id` bigint NOT NULL COMMENT '医生id',
  `period` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL COMMENT '1.上午|2.下午',
  `type_id` int DEFAULT '1' COMMENT '0.线上排班/1.线下排班',
  `slot_total` int DEFAULT '0' COMMENT '总号量',
  `slot_use` int DEFAULT '0' COMMENT '已使用号',
  `status` int DEFAULT NULL COMMENT '0.正常1.停诊',
  `create_at` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  `update_at` datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci;

-- ----------------------------
-- Records of clnic_staff
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
-- Table structure for medical_case
-- ----------------------------
DROP TABLE IF EXISTS `medical_case`;
CREATE TABLE `medical_case` (
  `id` bigint NOT NULL AUTO_INCREMENT COMMENT '主键',
  `patient_id` bigint NOT NULL COMMENT '患者ID（不做外键）',
  `chief_complaint` varchar(255) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL COMMENT '主诉',
  `history` tinytext COLLATE utf8mb3_unicode_ci COMMENT '现病史/既往史',
  `images` json DEFAULT NULL COMMENT '病例图片URL列表（JSON数组）',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  KEY `idx_patient` (`patient_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci COMMENT='病例表';

-- ----------------------------
-- Records of medical_case
-- ----------------------------
BEGIN;
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
